1 package com.android.attendance.db;
2
3 import java.util.ArrayList;
4
5 import com.android.attendance.bean.AttendanceBean;
6 import com.android.attendance.bean.AttendanceSessionBean;
7 import com.android.attendance.bean.FacultyBean;
8 import com.android.attendance.bean.StudentBean;
9
10 import android.content.Context;
11 import android.database.Cursor;
12 import android.database.sqlite.SQLiteDatabase;
13 import android.database.sqlite.SQLiteOpenHelper;
14 import android.util.Log;
15
16 public class DBAdapter extends SQLiteOpenHelper {
17
18 // All Static variables
19 // Database Version
20 private static final int DATABASE_VERSION = 1;
21
22 // Database Name
23 private static final String DATABASE_NAME = "Attendance";
24
25 // Contacts table name
26 private static final String FACULTY_INFO_TABLE = "faculty_table";
27 private static final String STUDENT_INFO_TABLE = "student_table";
28 private static final String ATTENDANCE_SESSION_TABLE = "attendance_session_table";
29 private static final String ATTENDANCE_TABLE = "attendance_table";
30
31
32 // Contacts Table Columns names
33 private static final String KEY_FACULTY_ID = "faculty_id";
34 private static final String KEY_FACULTY_FIRSTNAME = "faculty_firstname";
35 private static final String KEY_FACULTY_LASTNAME = "faculty_Lastname";
36 private static final String KEY_FACULTY_MO_NO = "faculty_mobilenumber";
37 private static final String KEY_FACULTY_ADDRESS = "faculty_address";
38 private static final String KEY_FACULTY_USERNAME = "faculty_username";
39 private static final String KEY_FACULTY_PASSWORD = "faculty_password";
40
41 private static final String KEY_STUDENT_ID = "student_id";
42 private static final String KEY_STUDENT_FIRSTNAME = "student_firstname";
43 private static final String KEY_STUDENT_LASTNAME = "student_lastname";
44 private static final String KEY_STUDENT_MO_NO = "student_mobilenumber";
45 private static final String KEY_STUDENT_ADDRESS = "student_address";
46 private static final String KEY_STUDENT_DEPARTMENT = "student_department";
47 private static final String KEY_STUDENT_CLASS = "student_class";
48
49 private static final String KEY_ATTENDANCE_SESSION_ID = "attendance_session_id";
50 private static final String KEY_ATTENDANCE_SESSION_FACULTY_ID = "attendance_session_faculty_id";
51 private static final String KEY_ATTENDANCE_SESSION_DEPARTMENT = "attendance_session_department";
52 private static final String KEY_ATTENDANCE_SESSION_CLASS = "attendance_session_class";
53 private static final String KEY_ATTENDANCE_SESSION_DATE = "attendance_session_date";
54 private static final String KEY_ATTENDANCE_SESSION_SUBJECT = "attendance_session_subject";
55
56 private static final String KEY_SESSION_ID = "attendance_session_id";
57 private static final String KEY_ATTENDANCE_STUDENT_ID = "attendance_student_id";
58 private static final String KEY_ATTENDANCE_STATUS = "attendance_status";
59
60
61 public DBAdapter(Context context) {
62 super(context, DATABASE_NAME, null, DATABASE_VERSION);
63 }
64
65
66 @Override
67
68 public void onCreate(SQLiteDatabase db) {
69 String queryFaculty="CREATE TABLE "+ FACULTY_INFO_TABLE +" (" +
70 KEY_FACULTY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
71 KEY_FACULTY_FIRSTNAME + " TEXT, " +
72 KEY_FACULTY_LASTNAME + " TEXT, " +
73 KEY_FACULTY_MO_NO + " TEXT, " +
74 KEY_FACULTY_ADDRESS + " TEXT," +
75 KEY_FACULTY_USERNAME + " TEXT," +
76 KEY_FACULTY_PASSWORD + " TEXT " + ")";
77 Log.d("queryFaculty",queryFaculty);
78
79
80 String queryStudent="CREATE TABLE "+ STUDENT_INFO_TABLE +" (" +
81 KEY_STUDENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
82 KEY_STUDENT_FIRSTNAME + " TEXT, " +
83 KEY_STUDENT_LASTNAME + " TEXT, " +
84 KEY_STUDENT_MO_NO + " TEXT, " +
85 KEY_STUDENT_ADDRESS + " TEXT," +
86 KEY_STUDENT_DEPARTMENT + " TEXT," +
87 KEY_STUDENT_CLASS + " TEXT " + ")";
88 Log.d("queryStudent",queryStudent );
89
90
91 String queryAttendanceSession="CREATE TABLE "+ ATTENDANCE_SESSION_TABLE +" (" +
92 KEY_ATTENDANCE_SESSION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
93 KEY_ATTENDANCE_SESSION_FACULTY_ID + " INTEGER, " +
94 KEY_ATTENDANCE_SESSION_DEPARTMENT + " TEXT, " +
95 KEY_ATTENDANCE_SESSION_CLASS + " TEXT, " +
96 KEY_ATTENDANCE_SESSION_DATE + " DATE," +
97 KEY_ATTENDANCE_SESSION_SUBJECT + " TEXT" + ")";
98 Log.d("queryAttendanceSession",queryAttendanceSession );
99
100
101 String queryAttendance="CREATE TABLE "+ ATTENDANCE_TABLE +" (" +
102 KEY_SESSION_ID + " INTEGER, " +
103 KEY_ATTENDANCE_STUDENT_ID + " INTEGER, " +
104 KEY_ATTENDANCE_STATUS + " TEXT " + ")";
105 Log.d("queryAttendance",queryAttendance );
106
107
108 try
109 {
110 db.execSQL(queryFaculty);
111 db.execSQL(queryStudent);
112 db.execSQL(queryAttendanceSession);
113 db.execSQL(queryAttendance);
114 }
115 catch (Exception e) {
116 e.printStackTrace();
117 Log.e("Exception", e.getMessage());
118 }
119
120 }
121
122
123 @Override
124 public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
125 String queryFaculty="CREATE TABLE "+ FACULTY_INFO_TABLE +" (" +
126 KEY_FACULTY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
127 KEY_FACULTY_FIRSTNAME + " TEXT, " +
128 KEY_FACULTY_LASTNAME + " TEXT, " +
129 KEY_FACULTY_MO_NO + " TEXT, " +
130 KEY_FACULTY_ADDRESS + " TEXT," +
131 KEY_FACULTY_USERNAME + " TEXT," +
132 KEY_FACULTY_PASSWORD + " TEXT " + ")";
133 Log.d("queryFaculty",queryFaculty);
134
135
136 String queryStudent="CREATE TABLE "+ STUDENT_INFO_TABLE +" (" +
137 KEY_STUDENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
138 KEY_STUDENT_FIRSTNAME + " TEXT, " +
139 KEY_STUDENT_LASTNAME + " TEXT, " +
140 KEY_STUDENT_MO_NO + " TEXT, " +
141 KEY_STUDENT_ADDRESS + " TEXT," +
142 KEY_STUDENT_DEPARTMENT + " TEXT," +
143 KEY_STUDENT_CLASS + " TEXT " + ")";
144 Log.d("queryStudent",queryStudent );
145
146
147 String queryAttendanceSession="CREATE TABLE "+ ATTENDANCE_SESSION_TABLE +" (" +
148 KEY_ATTENDANCE_SESSION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
149 KEY_ATTENDANCE_SESSION_FACULTY_ID + " INTEGER, " +
150 KEY_ATTENDANCE_SESSION_DEPARTMENT + " TEXT, " +
151 KEY_ATTENDANCE_SESSION_CLASS + " TEXT, " +
152 KEY_ATTENDANCE_SESSION_DATE + " TEXT," +
153 KEY_ATTENDANCE_SESSION_SUBJECT + " TEXT" +")";
154 Log.d("queryAttendanceSession",queryAttendanceSession );
155
156
157 String queryAttendance="CREATE TABLE "+ ATTENDANCE_TABLE +" (" +
158 KEY_SESSION_ID + " INTEGER, " +
159 KEY_ATTENDANCE_STUDENT_ID + " INTEGER, " +
160 KEY_ATTENDANCE_STATUS + " TEXT " + ")";
161 Log.d("queryAttendance",queryAttendance );
162
163 try
164 {
165 db.execSQL(queryFaculty);
166 db.execSQL(queryStudent);
167 db.execSQL(queryAttendanceSession);
168 db.execSQL(queryAttendance);
169 }
170 catch (Exception e) {
171 e.printStackTrace();
172 Log.e("Exception", e.getMessage());
173 }
174 }
175
176 //facult crud
177 public void addFaculty(FacultyBean facultyBean) {
178 SQLiteDatabase db = this.getWritableDatabase();
179
180 String query = "INSERT INTO faculty_table (faculty_firstname,faculty_Lastname,faculty_mobilenumber,faculty_address,faculty_username,faculty_password) values ('"+
181 facultyBean.getFaculty_firstname()+"', '"+
182 facultyBean.getFaculty_lastname()+"', '"+
183 facultyBean.getFaculty_mobilenumber()+"', '"+
184 facultyBean.getFaculty_address()+"', '"+
185 facultyBean.getFaculty_username()+"', '"+
186 facultyBean.getFaculty_password()+"')";
187 Log.d("query", query);
188 db.execSQL(query);
189 db.close();
190 }
191
192 public FacultyBean validateFaculty(String userName,String password)
193 {
194 SQLiteDatabase db = this.getWritableDatabase();
195
196 String query = "SELECT * FROM faculty_table where faculty_username='"+userName+"' and faculty_password='"+password+"'";
197 Cursor cursor = db.rawQuery(query, null);
198
199 if(cursor.moveToFirst())
200 {
201
202 FacultyBean facultyBean = new FacultyBean();
203 facultyBean.setFaculty_id(Integer.parseInt(cursor.getString(0)));
204 facultyBean.setFaculty_firstname(cursor.getString(1));
205 facultyBean.setFaculty_lastname(cursor.getString(2));
206 facultyBean.setFaculty_mobilenumber(cursor.getString(3));
207 facultyBean.setFaculty_address(cursor.getString(4));
208 facultyBean.setFaculty_username(cursor.getString(5));
209 facultyBean.setFaculty_password(cursor.getString(6));
210 return facultyBean;
211 }
212 return null;
213 }
214
215 public ArrayList<FacultyBean> getAllFaculty()
216 {
217 Log.d("in get all","in get all" );
218 ArrayList<FacultyBean> list = new ArrayList<FacultyBean>();
219
220 SQLiteDatabase db = this.getWritableDatabase();
221 String query = "SELECT * FROM faculty_table";
222 Cursor cursor = db.rawQuery(query, null);
223
224 if(cursor.moveToFirst())
225 {
226 do{
227 FacultyBean facultyBean = new FacultyBean();
228 facultyBean.setFaculty_id(Integer.parseInt(cursor.getString(0)));
229 facultyBean.setFaculty_firstname(cursor.getString(1));
230 facultyBean.setFaculty_lastname(cursor.getString(2));
231 facultyBean.setFaculty_mobilenumber(cursor.getString(3));
232 facultyBean.setFaculty_address(cursor.getString(4));
233 facultyBean.setFaculty_username(cursor.getString(5));
234 facultyBean.setFaculty_password(cursor.getString(6));
235 list.add(facultyBean);
236
237 }while(cursor.moveToNext());
238 }
239 return list;
240 }
241
242 public void deleteFaculty(int facultyId) {
243 SQLiteDatabase db = this.getWritableDatabase();
244
245 String query = "DELETE FROM faculty_table WHERE faculty_id="+facultyId ;
246
247 Log.d("query", query);
248 db.execSQL(query);
249 db.close();
250 }
251
252 //student crud
253 public void addStudent(StudentBean studentBean) {
254 SQLiteDatabase db = this.getWritableDatabase();
255
256 String query = "INSERT INTO student_table (student_firstname,student_lastname,student_mobilenumber,student_address,student_department,student_class) values ('"+
257 studentBean.getStudent_firstname()+"', '"+
258 studentBean.getStudent_lastname()+"','"+
259 studentBean.getStudent_mobilenumber()+"', '"+
260 studentBean.getStudent_address()+"', '"+
261 studentBean.getStudent_department()+"', '"+
262 studentBean.getStudent_class()+"')";
263 Log.d("query", query);
264 db.execSQL(query);
265 db.close();
266 }
267
268 public ArrayList<StudentBean> getAllStudent()
269 {
270 ArrayList<StudentBean> list = new ArrayList<StudentBean>();
271
272 SQLiteDatabase db = this.getWritableDatabase();
273 String query = "SELECT * FROM student_table";
274 Cursor cursor = db.rawQuery(query, null);
275
276 if(cursor.moveToFirst())
277 {
278 do{
279 StudentBean studentBean = new StudentBean();
280 studentBean.setStudent_id(Integer.parseInt(cursor.getString(0)));
281 studentBean.setStudent_firstname(cursor.getString(1));
282 studentBean.setStudent_lastname(cursor.getString(2));
283 studentBean.setStudent_mobilenumber(cursor.getString(3));
284 studentBean.setStudent_address(cursor.getString(4));
285 studentBean.setStudent_department(cursor.getString(5));
286 studentBean.setStudent_class(cursor.getString(6));
287 list.add(studentBean);
288 }while(cursor.moveToNext());
289 }
290 return list;
291 }
292
293 public ArrayList<StudentBean> getAllStudentByBranchYear(String branch,String year)
294 {
295 ArrayList<StudentBean> list = new ArrayList<StudentBean>();
296
297 SQLiteDatabase db = this.getWritableDatabase();
298 String query = "SELECT * FROM student_table where student_department='"+branch+"' and student_class='"+year+"'";
299 Cursor cursor = db.rawQuery(query, null);
300
301 if(cursor.moveToFirst())
302 {
303 do{
304 StudentBean studentBean = new StudentBean();
305 studentBean.setStudent_id(Integer.parseInt(cursor.getString(0)));
306 studentBean.setStudent_firstname(cursor.getString(1));
307 studentBean.setStudent_lastname(cursor.getString(2));
308 studentBean.setStudent_mobilenumber(cursor.getString(3));
309 studentBean.setStudent_address(cursor.getString(4));
310 studentBean.setStudent_department(cursor.getString(5));
311 studentBean.setStudent_class(cursor.getString(6));
312 list.add(studentBean);
313 }while(cursor.moveToNext());
314 }
315 return list;
316 }
317
318 public StudentBean getStudentById(int studentId)
319 {
320 StudentBean studentBean = new StudentBean();
321 SQLiteDatabase db = this.getWritableDatabase();
322 String query = "SELECT * FROM student_table where student_id="+studentId;
323 Cursor cursor = db.rawQuery(query, null);
324
325 if(cursor.moveToFirst())
326 {
327 do{
328
329 studentBean.setStudent_id(Integer.parseInt(cursor.getString(0)));
330 studentBean.setStudent_firstname(cursor.getString(1));
331 studentBean.setStudent_lastname(cursor.getString(2));
332 studentBean.setStudent_mobilenumber(cursor.getString(3));
333 studentBean.setStudent_address(cursor.getString(4));
334 studentBean.setStudent_department(cursor.getString(5));
335 studentBean.setStudent_class(cursor.getString(6));
336
337 }while(cursor.moveToNext());
338 }
339 return studentBean;
340 }
341
342 public void deleteStudent(int studentId) {
343 SQLiteDatabase db = this.getWritableDatabase();
344
345 String query = "DELETE FROM student_table WHERE student_id="+studentId ;
346
347 Log.d("query", query);
348 db.execSQL(query);
349 db.close();
350 }
351
352 //attendance session Table crud
353 public int addAttendanceSession(AttendanceSessionBean attendanceSessionBean) {
354 SQLiteDatabase db = this.getWritableDatabase();
355
356 String query = "INSERT INTO attendance_session_table (attendance_session_faculty_id,attendance_session_department,attendance_session_class,attendance_session_date,attendance_session_subject) values ('"+
357 attendanceSessionBean.getAttendance_session_faculty_id()+"', '"+
358 attendanceSessionBean.getAttendance_session_department()+"','"+
359 attendanceSessionBean.getAttendance_session_class()+"', '"+
360 attendanceSessionBean.getAttendance_session_date()+"', '"+
361 attendanceSessionBean.getAttendance_session_subject()+"')";
362 Log.d("query", query);
363 db.execSQL(query);
364
365 String query1= "select max(attendance_session_id) from attendance_session_table";
366 Cursor cursor = db.rawQuery(query1, null);
367
368 if(cursor.moveToFirst())
369 {
370 int sessionId = Integer.parseInt(cursor.getString(0));
371
372 return sessionId;
373 }
374
375
376 db.close();
377 return 0;
378 }
379
380 public ArrayList<AttendanceSessionBean> getAllAttendanceSession()
381 {
382 ArrayList<AttendanceSessionBean> list = new ArrayList<AttendanceSessionBean>();
383
384 SQLiteDatabase db = this.getWritableDatabase();
385 String query = "SELECT * FROM attendance_session_table";
386 Cursor cursor = db.rawQuery(query, null);
387
388 if(cursor.moveToFirst())
389 {
390 do{
391 AttendanceSessionBean attendanceSessionBean = new AttendanceSessionBean();
392 attendanceSessionBean.setAttendance_session_id(Integer.parseInt(cursor.getString(0)));
393 attendanceSessionBean.setAttendance_session_faculty_id(Integer.parseInt(cursor.getString(1)));
394 attendanceSessionBean.setAttendance_session_department(cursor.getString(2));
395 attendanceSessionBean.setAttendance_session_class(cursor.getString(3));
396 attendanceSessionBean.setAttendance_session_date(cursor.getString(4));
397 attendanceSessionBean.setAttendance_session_subject(cursor.getString(5));
398 list.add(attendanceSessionBean);
399 }while(cursor.moveToNext());
400 }
401 return list;
402 }
403
404 public void deleteAttendanceSession(int attendanceSessionId) {
405 SQLiteDatabase db = this.getWritableDatabase();
406
407 String query = "DELETE FROM attendance_session_table WHERE attendance_session_id="+attendanceSessionId ;
408
409 Log.d("query", query);
410 db.execSQL(query);
411 db.close();
412 }
413 //attendance crud
414 public void addNewAttendance(AttendanceBean attendanceBean) {
415 SQLiteDatabase db = this.getWritableDatabase();
416
417 String query = "INSERT INTO attendance_table values ("+
418 attendanceBean.getAttendance_session_id()+", "+
419 attendanceBean.getAttendance_student_id()+", '"+
420 attendanceBean.getAttendance_status()+"')";
421 Log.d("query", query);
422 db.execSQL(query);
423 db.close();
424 }
425
426
427 public ArrayList<AttendanceBean> getAttendanceBySessionID(AttendanceSessionBean attendanceSessionBean)
428 {
429 int attendanceSessionId=0;
430 ArrayList<AttendanceBean> list = new ArrayList<AttendanceBean>();
431
432 SQLiteDatabase db = this.getWritableDatabase();
433 String query = "SELECT * FROM attendance_session_table where attendance_session_faculty_id="+attendanceSessionBean.getAttendance_session_faculty_id()+""
434 +" AND attendance_session_department='"+attendanceSessionBean.getAttendance_session_department()+"' AND attendance_session_class='"+attendanceSessionBean.getAttendance_session_class()+"'" +
435 " AND attendance_session_date='"+attendanceSessionBean.getAttendance_session_date()+"' AND attendance_session_subject='"+attendanceSessionBean.getAttendance_session_subject()+"'";
436 Cursor cursor = db.rawQuery(query, null);
437
438 if(cursor.moveToFirst())
439 {
440 do{
441 attendanceSessionId=(Integer.parseInt(cursor.getString(0)));
442 }while(cursor.moveToNext());
443 }
444
445 String query1="SELECT * FROM attendance_table where attendance_session_id=" + attendanceSessionId+" order by attendance_student_id";
446 Cursor cursor1 = db.rawQuery(query1, null);
447 if(cursor1.moveToFirst())
448 {
449 do{
450 AttendanceBean attendanceBean = new AttendanceBean();
451 attendanceBean.setAttendance_session_id(Integer.parseInt(cursor1.getString(0)));
452 attendanceBean.setAttendance_student_id(Integer.parseInt(cursor1.getString(1)));
453 attendanceBean.setAttendance_status(cursor1.getString(2));
454 list.add(attendanceBean);
455
456 }while(cursor1.moveToNext());
457 }
458 return list;
459 }
460
461 public ArrayList<AttendanceBean> getTotalAttendanceBySessionID(AttendanceSessionBean attendanceSessionBean)
462 {
463 int attendanceSessionId=0;
464 ArrayList<AttendanceBean> list = new ArrayList<AttendanceBean>();
465
466 SQLiteDatabase db = this.getWritableDatabase();
467 String query = "SELECT * FROM attendance_session_table where attendance_session_faculty_id="+attendanceSessionBean.getAttendance_session_faculty_id()+""
468 +" AND attendance_session_department='"+attendanceSessionBean.getAttendance_session_department()+"' AND attendance_session_class='"+attendanceSessionBean.getAttendance_session_class()+"'" +
469 " AND attendance_session_subject='"+attendanceSessionBean.getAttendance_session_subject()+"'";
470 Cursor cursor = db.rawQuery(query, null);
471
472 if(cursor.moveToFirst())
473 {
474 do{
475 attendanceSessionId=(Integer.parseInt(cursor.getString(0)));
476
477 String query1="SELECT * FROM attendance_table where attendance_session_id=" + attendanceSessionId+" order by attendance_student_id";
478 Cursor cursor1 = db.rawQuery(query1, null);
479 if(cursor1.moveToFirst())
480 {
481 do{
482 AttendanceBean attendanceBean = new AttendanceBean();
483 attendanceBean.setAttendance_session_id(Integer.parseInt(cursor1.getString(0)));
484 attendanceBean.setAttendance_student_id(Integer.parseInt(cursor1.getString(1)));
485 attendanceBean.setAttendance_status(cursor1.getString(2));
486 list.add(attendanceBean);
487
488 }while(cursor1.moveToNext());
489 }
490
491 AttendanceBean attendanceBean = new AttendanceBean();
492 attendanceBean.setAttendance_session_id(0);
493 attendanceBean.setAttendance_status("Date : " + cursor.getString(4));
494 list.add(attendanceBean);
495
496 }while(cursor.moveToNext());
497 }
498
499
500 return list;
501 }
502
503 public ArrayList<AttendanceBean> getAllAttendanceByStudent()
504 {
505 ArrayList<AttendanceBean> list = new ArrayList<AttendanceBean>();
506
507 SQLiteDatabase db = this.getWritableDatabase();
508 String query = "SELECT attendance_student_id,count(*) FROM attendance_table where attendance_status='P' group by attendance_student_id";
509
510 Log.d("query", query);
511
512 Cursor cursor = db.rawQuery(query, null);
513
514
515
516 if(cursor.moveToFirst())
517 {
518 do{
519 Log.d("studentId","studentId:"+cursor.getString(0)+", Count:"+cursor.getString(1));
520 AttendanceBean attendanceBean = new AttendanceBean();
521 attendanceBean.setAttendance_student_id(Integer.parseInt(cursor.getString(0)));
522 attendanceBean.setAttendance_session_id(Integer.parseInt(cursor.getString(1)));
523 list.add(attendanceBean);
524
525 }while(cursor.moveToNext());
526 }
527 return list;
528 }
529 /*public ArrayList<AttendanceBean> getAllAttendanceBySessionID(int sessionId)
530 {
531 ArrayList<AttendanceBean> list = new ArrayList<AttendanceBean>();
532
533 SQLiteDatabase db = this.getWritableDatabase();
534 String query = "SELECT * FROM attendance_table where attendance_session_id=" + sessionId;
535 Cursor cursor = db.rawQuery(query, null);
536
537 if(!cursor.moveToFirst())
538 {
539 do{
540 AttendanceBean attendanceBean = new AttendanceBean();
541 attendanceBean.setAttendance_session_id(Integer.parseInt(cursor.getString(0)));
542 attendanceBean.setAttendance_student_id(Integer.parseInt(cursor.getString(1)));
543 attendanceBean.setAttendance_status(cursor.getString(2));
544 list.add(attendanceBean);
545
546 }while(cursor.moveToNext());
547 }
548 return list;
549 }*/
550
551
552
553
554 // Creating Tables
555 /*@Override
556 public void onCreate(SQLiteDatabase db) {
557 String CREATE_User_Info_TABLE = "CREATE TABLE " + TABLE_INFO_USER + "("
558 + KEY_ID + " INTEGER PRIMARY KEY, " + KEY_FIRSTNAME + " TEXT, "+ KEY_LASTNAME + " TEXT, " +KEY_MO_NO +" TEXT, "
559 +KEY_EMAIL +" TEXT, " +KEY_USERNAME +" TEXT, " + KEY_PASSWORD +" TEXT " + ")";
560
561 Log.d("rupali",CREATE_User_Info_TABLE );
562 db.execSQL(CREATE_User_Info_TABLE);
563 }
564
565 // Upgrading database
566 @Override
567 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
568 // Drop older table if existed
569 db.execSQL("DROP TABLE IF EXISTS " + TABLE_INFO_USER);
570
571 // Create tables again
572 onCreate(db);
573 }
574
575 *//**
576 * All CRUD(Create, Read, Update, Delete) Operations
577 *//*
578
579
580
581 void addUserInfo(UserInfo userinfo) {
582 SQLiteDatabase db = this.getWritableDatabase();
583
584 ContentValues values = new ContentValues();
585 values.put(KEY_FIRSTNAME, userinfo.getUser_Firstname()); // Name
586 values.put(KEY_LASTNAME, userinfo.getUser_Lastname()); // Name
587 values.put(KEY_MO_NO, userinfo.getUser_MobileNo()); // Contact Phone
588 values.put(KEY_EMAIL, userinfo.getUser_EmailId());
589 values.put(KEY_USERNAME, userinfo.getUser_Username());
590 values.put(KEY_PASSWORD, userinfo.getUser_Password());
591
592 // Inserting Row
593 db.insert(TABLE_INFO_USER, null, values);
594 //2nd argument is String containing nullColumnHack
595 db.close(); // Closing database connection
596 }
597
598
599 // Getting single contact
600 UserInfo getUserInfo(int id) {
601 SQLiteDatabase db = this.getReadableDatabase();
602
603 Cursor cursor = db.query(TABLE_INFO_USER, new String[] { KEY_ID,
604 KEY_FIRSTNAME, KEY_LASTNAME,KEY_MO_NO, KEY_EMAIL, KEY_USERNAME, KEY_PASSWORD }, KEY_ID + "=?",
605 new String[] { String.valueOf(id) }, null, null, null, null);
606 if (cursor != null)
607 cursor.moveToFirst();
608
609 UserInfo userinfo = new UserInfo(Integer.parseInt(cursor.getString(0)),
610 cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getString(5),cursor.getString(6));
611 // return contact
612 return userinfo;
613 }
614
615 public UserInfo validateUser(String username, String password)
616 {
617 SQLiteDatabase db = this.getReadableDatabase();
618 String query = "Select * from User_Info_Table WHERE User_Username='"+ username +"' AND User_Password='"+password+"'";
619 Log.d("Rupali", "Login QUERY:" + query);
620
621 Cursor cursor = db.rawQuery(query, null);
622
623
624 if(!cursor.moveToFirst())
625 {
626 Log.d("Rupali", "cursor is null.. returing NULL");
627 return null;
628 }
629 Log.d("Rupali", "cursor is NOT null.. we got user data...");
630
631
632 UserInfo userinfo = new UserInfo(Integer.parseInt(cursor.getString(0)),
633 cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getString(5),cursor.getString(6));
634
635 return userinfo;
636 }
637
638 // Updating single contact
639 public int updateUserPassword(UserInfo userinfo) {
640 SQLiteDatabase db = this.getWritableDatabase();
641
642 ContentValues values = new ContentValues();
643 values.put(KEY_PASSWORD, userinfo.getUser_Password());
644
645
646 // updating row
647 return db.update(TABLE_INFO_USER, values, KEY_ID + " = ?",
648 new String[] { String.valueOf(userinfo.getUser_id()) });
649 }
650
651 public int updateUserContact(UserInfo userinfo) {
652 SQLiteDatabase db = this.getWritableDatabase();
653
654 ContentValues values = new ContentValues();
655 values.put(KEY_MO_NO, userinfo.getUser_MobileNo());
656 values.put(KEY_EMAIL, userinfo.getUser_EmailId());
657
658
659 // updating row
660 return db.update(TABLE_INFO_USER, values, KEY_ID + " = ?",
661 new String[] { String.valueOf(userinfo.getUser_id()) });
662 }
663
664
665 //veiw details
666
667 public UserInfo viewUserInfo(String id) {
668 SQLiteDatabase db = this.getReadableDatabase();
669
670 String query = "Select * from User_Info_Table WHERE id='"+id+"'";
671 Cursor cursor = db.rawQuery(query, null);
672 if(!cursor.moveToFirst())
673 {
674 Log.d("Rupali", "cursor is null.. returing NULL");
675 return null;
676 }
677 Log.d("Rupali", "cursor is NOT null.. we got user data...");
678
679 UserInfo userinfo = new UserInfo(Integer.parseInt(cursor.getString(0)),
680 cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getString(5),cursor.getString(6));
681 // return contact
682 return userinfo;
683 }
684
685
686
687 // Getting All users
688 public List<UserInfo> getAllUserInfo() {
689 List<UserInfo> userinfolist = new ArrayList<UserInfo>();
690 // Select All Query
691 String selectQuery = "SELECT * FROM " + TABLE_INFO_USER;
692
693 SQLiteDatabase db = this.getWritableDatabase();
694 Cursor cursor = db.rawQuery(selectQuery, null);
695
696 // looping through all rows and adding to list
697 if (cursor.moveToFirst()) {
698 do {
699
700 UserInfo userinfo=new UserInfo();
701
702 userinfo.setUser_id(Integer.parseInt(cursor.getString(0)));
703 userinfo.setUser_Lastname(cursor.getString(2));
704 userinfo.setUser_Username(cursor.getString(5));
705 userinfo.setUser_Firstname(cursor.getString(1));
706
707
708
709 // Adding contact to list
710 userinfolist.add(userinfo);
711 } while (cursor.moveToNext());
712 }
713
714 // return contact list
715 return userinfolist;
716 }
717
718 // Deleting single contact
719 public void deleteUser(UserInfo userinfo) {
720 SQLiteDatabase db = this.getWritableDatabase();
721 db.delete(TABLE_INFO_USER, KEY_ID + " = ?",
722 new String[] { String.valueOf(userinfo.getUser_id()) });
723 db.close();
724 }
725 */
726 }
